/**
 * $ HEADER: $
 */
package net.frankish.moviebank.back.db;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Random;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.TreeSet;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import net.frankish.moviebank.back.PersistenceManager;
import net.frankish.moviebank.beans.Genre;
import net.frankish.moviebank.beans.Movie;

/**
 * @author Frankish
 * @version $ Revision: $
 * @6-May-08
 */
public class DerbyDatabase {

	private static String sqlCreateMovieTable =
		"CREATE table MB.MOVIE (ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+
        "TITLE VARCHAR(150),"+
        "GENRE VARCHAR(255),"+
        "PRODUCER VARCHAR(50),"+
        "RELEASE VARCHAR(20),"+
        "ACTORS VARCHAR(500),"+
        "COVERPICTURE BLOB(250 K),"+
        "COVERPICTUREFILENAME VARCHAR(128),"+
        "DESCRIPTION VARCHAR(5000),"+
        "IDENTIFIER VARCHAR(100) )";
	private static String sqlIndexMovie = "CREATE UNIQUE INDEX MOVIE_TITLE_RELEASE ON MB.MOVIE (TITLE, RELEASE, IDENTIFIER)";
	private static String sqlCreateGenreTable =
		"CREATE table MB.GENRE (ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+
        "GENRE_NAME VARCHAR(25), DESCRIPTION VARCHAR(255))";

	private static String sqlMovieCount = "SELECT COUNT(ID) AS COUNT FROM MB.MOVIE";
	private static Connection dbConnection;


	public static void start(){
		System.setProperty("derby.system.home", "./db");
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void create(){
		Connection dbConnection = null;
		String strUrl = "jdbc:derby:MovieBankDatabse;create=true";
		try {
		    dbConnection = DriverManager.getConnection(strUrl);

		    createTable(dbConnection,sqlCreateMovieTable, "Movie");
		    createTable(dbConnection,sqlIndexMovie, "MovieIndex");
		    createTable(dbConnection,sqlCreateGenreTable, "Genre");

		} catch (SQLException ex) {
		    ex.printStackTrace();
		}
	}

	public static Connection getConnection(){
		String strUrl = "jdbc:derby:MovieBankDatabse;";
		if(dbConnection == null){
			try {
			    dbConnection = DriverManager.getConnection(strUrl);
			} catch (SQLException ex) {
			    ex.printStackTrace();
			}
		}
		return dbConnection;
	}



	private static boolean createTable(Connection dbConnection, String str, String tableName) {
	    boolean bCreatedTables = false;
	    Statement statement = null;
	    try {
	        statement = dbConnection.createStatement();
	        statement.execute(str);
	        bCreatedTables = true;
	    } catch (SQLException ex) {
	        Logger.getLogger(DerbyDatabase.class.toString()).info("Table already exists ["+tableName+"]");
	    }

	    return bCreatedTables;
	}

	public static void convert() {
		HashMap<Class,HashMap> beans = PersistenceManager.getInstance().loadData();
		HashMap movies = beans.get(Movie.class);
		if(movies == null) return;
		ArrayList<Movie> values = new ArrayList(movies.values());
		if(values.size() <= 0)return;
		
		Set<String> genres = new TreeSet<String>();
		Logger.getLogger(DerbyDatabase.class.getName()).warning("Converting ["+values.size()+"] xml files to derby entries.");
		while(values.size()>0){
			Movie movie = null; 
			try {
				movie = values.remove(0);
				DerbyDatabase.insertMovie(movie);
				PersistenceManager.getInstance().remove(movie);
				for (String st : movie.getGenre()) {
					genres.add(st);
				}
			} catch (SQLException e) {
				Logger.getLogger(DerbyDatabase.class.getName()).log(Level.WARNING,"SQLException converting Movie["+(movie != null ? movie.getTitle() : "null")+"]",e);
			}
		}
		for (String string : genres) {
			 try {
				insertGenre(new Genre(string,string));
			} catch (SQLException e) {
				Logger.getLogger(DerbyDatabase.class.getName()).log(Level.WARNING,"SQLException converting Genre["+(string != null ? string : "null")+"]",e);
			}
		}
	}
	
	public static Vector<Movie> findAllMovie(){
		try{
			Statement st = getConnection().createStatement();
			ResultSet set = st.executeQuery("SELECT * FROM MB.MOVIE ORDER BY ID");
			Vector<Movie> results = new Vector<Movie>();
			while(set.next()){
				results.add(getMovieBean(set));
			}
	
			return results;
		}catch(SQLException ex){
			Logger.getLogger(DerbyDatabase.class.getName()).log(Level.SEVERE,"Exception loading movies",ex);
			return new Vector<Movie>();
		}
	}

	private static Movie getMovieBean(ResultSet set) {
		Movie bean = new Movie();
		try {
			bean.setActors(set.getString("ACTORS"));
			bean.setCoverPicture(set.getString("COVERPICTUREFILENAME"));
			Blob image = set.getBlob("COVERPICTURE");
			if(set.wasNull())
				bean.setImage(null);
			else
				bean.setImage(image.getBytes(1, (int)image.length()));
			bean.setDescription(set.getString("DESCRIPTION"));
			Vector<String> g = new Vector<String>();
			String genres = set.getString("GENRE");
			if(genres != null){
				StringTokenizer toks = new StringTokenizer(genres,";");
				while(toks.hasMoreTokens()){
					g.add(toks.nextToken());
				}
			}
			bean.setGenre(g);
			bean.setIdentifier(set.getString("IDENTIFIER"));
			bean.setId(set.getInt("ID"));
			bean.setKey(null);
			bean.setProducer(set.getString("PRODUCER"));
			bean.setRelease(set.getString("RELEASE"));
			bean.setTitle(set.getString("TITLE"));
		} catch (SQLException e) {
			Logger.getLogger(DerbyDatabase.class.getName()).log(Level.SEVERE,"Exception loading movies",e);
		}
		return bean;
	}

	public static void insertMovie(Movie bean) throws SQLException{
		PreparedStatement st = getConnection().prepareStatement("INSERT INTO MB.MOVIE (TITLE, GENRE, PRODUCER,RELEASE,ACTORS,COVERPICTURE,COVERPICTUREFILENAME, DESCRIPTION,IDENTIFIER) VALUES (?,?,?,?,?,?,?,?,?)");
		st.setString(1, bean.getTitle());
		StringBuffer genres = new StringBuffer();
		for (Object g : bean.getGenre()) {
			genres.append((String)g+";");
		}
		st.setString(2, genres.toString());
		st.setString(3, bean.getProducer());
		st.setString(4, bean.getRelease());
		st.setString(5, bean.getActors());

		if(bean.getImage() != null && bean.getImage().length > 0){
			Blob b = getConnection().createBlob();
			b.setBytes(1, bean.getImage());
			st.setBlob(6, b);
		}else
			st.setNull(6, java.sql.Types.BLOB);

		st.setString(7, bean.getCoverPicture());
		st.setString(8, bean.getDescription());
		st.setString(9, bean.getIdentifier());
		st.execute();

		bean.setId(findMovieID(bean.getTitle(),bean.getRelease(),bean.getIdentifier()));
	}

	private static int findMovieID(String title, String release, String identifier) throws SQLException {
		PreparedStatement st = getConnection().prepareStatement("SELECT ID FROM MB.MOVIE WHERE TITLE = ? AND IDENTIFIER = ? AND RELEASE = ?");
		st.setString(1, title);
		st.setString(2, identifier);
		st.setString(3, release);
		ResultSet rs = st.executeQuery();
		rs.next();
		return rs.getInt("ID");
	}

	public static void updateMovie(Movie bean) throws SQLException{
		PreparedStatement st = getConnection().prepareStatement("UPDATE MB.MOVIE SET TITLE=?,GENRE=?,PRODUCER=?,RELEASE=?,ACTORS=?,COVERPICTURE=?,COVERPICTUREFILENAME=?, DESCRIPTION=?,IDENTIFIER=? WHERE ID=?");
		st.setString(1, bean.getTitle());
		StringBuffer genres = new StringBuffer();
		for (String g : bean.getGenre()) {
			genres.append(g+";");
		}
		st.setString(2, genres.toString());
		st.setString(3, bean.getProducer());
		st.setString(4, bean.getRelease());
		st.setString(5, bean.getActors());

		if(bean.getImage() != null && bean.getImage().length > 0){
			Blob b = getConnection().createBlob();
			b.setBytes(1, bean.getImage());
			st.setBlob(6, b);
		}else
			st.setNull(6, java.sql.Types.BLOB);

		st.setString(7, bean.getCoverPicture());
		st.setString(8, bean.getDescription());
		st.setString(9, bean.getIdentifier());
		st.setInt(10, bean.getId());
		st.execute();
	}

	public static int countMovies() throws SQLException{
		ResultSet rs = getConnection().createStatement().executeQuery(sqlMovieCount);
		rs.next();
		return rs.getInt(1);
	}
	
	public static Movie findRandomMovie() throws SQLException{

		ResultSet rs = getConnection().createStatement().executeQuery("SELECT ID FROM MB.MOVIE");

		ArrayList<Integer> ints = new ArrayList<Integer>();
		while(rs.next())
			ints.add(rs.getInt(1));

		Random rand = new Random(System.currentTimeMillis());

		rs = getConnection().createStatement().executeQuery("SELECT * FROM MB.MOVIE WHERE ID = "+ints.get(rand.nextInt(ints.size())));
		if(rs.next())
			return getMovieBean(rs);

		return null;
	}

	public static Collection<Genre> findAllGenres() {
		try{
			Statement st = getConnection().createStatement();
			ResultSet set = st.executeQuery("SELECT * FROM MB.GENRE ORDER BY GENRE_NAME");
			Vector<Genre> results = new Vector<Genre>();
			while(set.next()){
				Genre bean = new Genre();
				bean.setId(set.getInt("ID"));
				bean.setName(set.getString("GENRE_NAME"));
				bean.setDescription(set.getString("DESCRIPTION"));
				results.add(bean);
			}
			return results;
		}catch(SQLException ex){
			Logger.getLogger(DerbyDatabase.class.getName()).log(Level.SEVERE,"Exception loading movies",ex);
			return new Vector<Genre>();
		}
	}
	public static void updateGenre(Genre bean) throws SQLException{
		PreparedStatement st = getConnection().prepareStatement("UPDATE MB.GENRE SET GENRE_NAME=?,DESCRIPTION=? WHERE ID=?");
		st.setString(1, bean.getName());
		st.setString(2, bean.getDescription());
		st.setInt(3, bean.getId());
		st.execute();
	}
	public static void insertGenre(Genre bean) throws SQLException{
		PreparedStatement st = getConnection().prepareStatement("INSERT INTO MB.GENRE (GENRE_NAME,DESCRIPTION) VALUES (?,?)");
		st.setString(1, bean.getName());
		st.setString(2, bean.getDescription());
		st.execute();
		bean.setId(findGenreID(bean.getName()));
	}

	private static int findGenreID(String name) throws SQLException {
			PreparedStatement st = getConnection().prepareStatement("SELECT ID FROM MB.GENRE WHERE GENRE_NAME = ?");
			st.setString(1, name);
			ResultSet rs = st.executeQuery();
			rs.next();
			return rs.getInt("ID");
	}

}
